#!/usr/bin/perl -w
###############################################################################
#  $Id$
#******************************************************************************
#  Copyright (C) 2007-2009  Lawrence Livermore National Security, LLC.
#  Produced at Lawrence Livermore National Laboratory (cf, DISCLAIMER).
#  Written by Adam Moody <moody20@llnl.gov> and
#             Mark Grondona <mgrondona@llnl.gov>
#
#  UCRL-CODE-235340.
#
#  This file is part of sqlog.
#
#  This is free software; you can redistribute it and/or modify it
#  under the terms of the GNU General Public License as published by
#  the Free Software Foundation; either version 2 of the License, or
#  (at your option) any later version.
#
#  This is distributed in the hope that it will be useful, but WITHOUT
#  ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
#  FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General Public License
#  for more details.
#
#  You should have received a copy of the GNU General Public License
#  along with this program; if not, see <http://www.gnu.org/licenses/>.
###############################################################################
#
#  sqlog - 'SLURM Queue Log' or 'SLURM Query Log' Utility
#
#############################################################################
use strict;
use lib qw();	# Required for _perl_libpaths RPM option
use DBI;
use File::Basename;
use Getopt::Long qw/ :config gnu_getopt ignore_case /;
use Hostlist qw/ expand compress intersect /;
use Date::Manip;

# Required for _path_env_var RPM option
$ENV{PATH} = '/bin:/usr/bin:/usr/sbin';

my $progname = basename $0;
my %conf;

#############################################################################
#
# Usage:
#
$conf{usage} = <<EOF
Usage: $progname [OPTIONS]...

Query information about jobs from the SLURM job log database and/or the 
current queue of running jobs.

    -j, --jobids=LIST...     Comma-separated list of jobids.
    -J, --job-names=LIST...  Comma-separated list of job names.
    -n, --nodes=LIST...      Comma-separated list of nodes or node lists.
    -p, --partitions=LIST... Comma-separated list of partitions.
    -s, --states=LIST...     Comma-separated list of job states. 
                              Use '--states=list' to list valid state names.
    -u, --users=LIST...      Comma-separated list of users.

        --regex              Following option is regex match instead of exact.

    -x, --exclude            Exclude the following list of jobids, users, 
                              states, partitions, or nodes.

    -N, --nnodes=N           List all jobs that ran on N nodes. N may be 
                              specified using the RANGE syntax described below.
        --minnodes=N         Explicitly specify the minimum number of nodes.
        --maxnodes=N         Explicitly specify the maximum number of nodes.

    -C, --ncores=N           List all jobs that ran on N cores. N may be 
                              specified using the RANGE syntax described below.
        --mincores=N         Explicitly specify the minimum number of cores.
        --maxcores=N         Explicitly specify the maximum number of cores.

    -T, --runtime=DURATION   List jobs that ran for DURATION, e.g., '4:30:00' or
                              '4h30m'. RANGE operators apply.
        --mintime=DURATION   Explicitly specify the minimum runtime.
        --maxtime=DURATION   Explicitly specify the maximum runtime.

    -t, --time, --at=TIME    List jobs which were running at a particular date 
                              and time, e.g., '04/14 13:30:00' or 'today,2pm'.
                              TIME may also be specified using RANGE operators.

    -S, --start=TIME         List all jobs that started at TIME.
        --start-before=TIME  Explicitly specify maximum start time.
        --start-after=TIME   Explicitly specify minimum start time.

    -E, --end=TIME           List all jobs that ended at TIME. 
        --end-before=TIME    Explicitly specify maximum end time.
        --end-after=TIME     Explicitly specify minimum end time.

    -X, --no-running         Don't query running jobs (no current queue).
        --no-db              Include only running jobs (don't query joblog DB).

    -H, --no-header          Don't print a header row.
    -o, --format=LIST        Specify a list of format keys to display or a 
                              format type, or both using the form 'TYPE:keys,..'
                              Use --format=list to list valid keys and types.

    -P, --sort=LIST          Specify a list of keys to sort output.

    -L, --limit=N            Limit the number of records to report (default=25).
    -a, --all                Report all matching records (Same as --limit=0).

    -h, --help               Display this message.
    -v, --verbose            Increase output verbosity.
        --dry-run            Don't actually do anything.

TIME, DURATION, and NUMERIC arguments may optionally use one of the RANGE 
operators +, -, or '..', where 
   +N      N or more (at N or later)
   -N      N or less (at N or earlier)
 N..M      Between N and M, inclusive
 N or \@N   Exactly N (exactly at N) (use @ if 'N' begins with '+' or '-').

LIST refers to a comma-separated list of words. All options except --format
which take a LIST argument may also be specified multiple times 
(e.g. --users=sally,tim --users=frank). Node lists may be specified using
the host list form, e.g. "host[34-36,67]".

TIME arguments are parsed using the perl Date::Manip(3pm) package, and thus
may be specified in one of many formats. Examples include '12pm', 
'yesterday,noon', '12/25-15:30:33', and so on. See the Date::Manip(3pm)
manpage for more examples.

EOF
;
 
#############################################################################
#
# Configuration:
#

# 
# DB configuration defaults 
#
$conf{db}         = "slurm";
$conf{sqluser}    = "slurm_read";
$conf{sqlpass}    = "";
$conf{sqlhost}    = "sqlhost";  
$conf{confdir}    = "/etc/slurm";

#   Enables / disables node tracking per job in version 2 schema
$conf{track}      = 1;

#   Assume neither version 1 nor version 2 are available
$conf{version}{1} = 0;
$conf{version}{2} = 0;

#  Default max number of rows to fetch:
$conf{limit}      = 25;

#  Default format type:
$conf{formattype} = "short";

#  Valid sort keys:
@{$conf{dbnames}} = qw/ id jobid user uid name state partition limit 
                        start end nodes nnodes ncores runtime /;
#  Default sort keys:
@{$conf{sortkeys}} = qw/ start /;

#  Map of sort keys to DB fields
%{$conf{sortfield}}= ( "id" => "id", "jobid" => "jobid", "user" => "username",
                       "uid" => "userid", "name" => "jobname", 
                       "state" => "jobstate", "partition" => "partition",
                       "limit" => "timelimit", "start" => "starttime",
                       "end" => "endtime", "nodes" => "nodelist",
                       "nnodes" => "nodecount", "ncores" => "corecount",
                       "runtime" => "runtime" );

#  Array for results:
@{$conf{results}}= ();

#############################################################################
#
#  Output formats:

#
#  Default format key lists. May be overridded in system and user config.
#
%{$conf{format_list}} = (
  "short" => 
    "jobid,part,name,user,state,start,runtime,nnodes,nodes,",
  "long"  => 
    "jobid,part,name,user,state,longstart,longend,runtime,ncores,nnodes,nodes",
  "freeform" => 
    "jobid,part,name,user,state,longstart,longend,runtime,ncores,nnodes,nodes"
);

#
#  Format specification for all format types
#   each entry has the form Name => { Header, format char, width -1 }
#

$conf{fmt_spec}{short} = { 
    "jobid"     => { "name" => "JOBID",    "ch" => '>', "l" => 6 },
    "user"      => { "name" => "USER",     "ch" => '>', "l" => 7 },
    "name"      => { "name" => "NAME",     "ch" => ">", "l" => 8 },
    "state"     => { "name" => "ST",       "ch" => ">", "l" => 2 },
    "partition" => { "name" => "PART",     "ch" => ">", "l" => 5 },
    "start"     => { "name" => "START",    "ch" => ">", "l" =>13 },
    "runtime"   => { "name" => "TIME",     "ch" => ">", "l" => 9 },
    "runtime_s" => { "name" => "TIME(s)",  "ch" => ">", "l" => 9 },
    "ncores"    => { "name" => "C",        "ch" => ">", "l" => 5 },
    "nnodes"    => { "name" => "N",        "ch" => ">", "l" => 3 },
    "nodes"     => { "name" => "NODELIST", "ch" => "*", "l" => 1 },
    "end"       => { "name" => "END",      "ch" => ">", "l" =>13 },
    "longstart" => { "name" => "START",    "ch" => ">", "l" =>18 },
    "longend"   => { "name" => "END",      "ch" => ">", "l" =>18 },
    "unixstart" => { "name" => "START",    "ch" => ">", "l" =>12 },
    "unixend"   => { "name" => "END",      "ch" => ">", "l" =>12 }
    };

$conf{fmt_spec}{long} = {
    "jobid"     => { "name" => "JOBID",    "ch" => '>', "l" => 6 },
    "user"      => { "name" => "USER",     "ch" => '>', "l" => 7 },
    "name"      => { "name" => "JOBNAME",  "ch" => ">", "l" => 20},
    "state"     => { "name" => "STATE",    "ch" => ">", "l" => 8 },
    "partition" => { "name" => "PARTITION","ch" => ">", "l" => 8 },
    "start"     => { "name" => "START",    "ch" => ">", "l" =>13 },
    "runtime"   => { "name" => "TIME",     "ch" => ">", "l" => 8 },
    "runtime_s" => { "name" => "TIME(s)",  "ch" => ">", "l" => 8 },
    "ncores"    => { "name" => "CORES",    "ch" => ">", "l" => 6 },
    "nnodes"    => { "name" => "NODES",    "ch" => ">", "l" => 4 },
    "nodes"     => { "name" => "NODELIST", "ch" => "*", "l" => 1 },
    "end"       => { "name" => "END",      "ch" => ">", "l" =>13 },
    "longstart" => { "name" => "START",    "ch" => ">", "l" =>18 },
    "longend"   => { "name" => "END",      "ch" => ">", "l" =>18 },
    "unixstart" => { "name" => "START",    "ch" => ">", "l" =>12 },
    "unixend"   => { "name" => "END",      "ch" => ">", "l" =>12 }
   };

$conf{fmt_spec}{freeform} = {
    "jobid"     => { "name" => "JOBID",    "ch" => '*', "l" => 1 },
    "user"      => { "name" => "USER",     "ch" => '*', "l" => 1 },
    "name"      => { "name" => "JOBNAME",  "ch" => "*", "l" => 1 },
    "state"     => { "name" => "STATE",    "ch" => "*", "l" => 1 },
    "partition" => { "name" => "PARTITION","ch" => "*", "l" => 1 },
    "start"     => { "name" => "START",    "ch" => "*", "l" => 1 },
    "runtime"   => { "name" => "TIME",     "ch" => "*", "l" => 1 },
    "runtime_s" => { "name" => "TIME(s)",  "ch" => "*", "l" => 1 },
    "ncores"    => { "name" => "CORES",    "ch" => "*", "l" => 1 },
    "nnodes"    => { "name" => "NODES",    "ch" => "*", "l" => 1 },
    "nodes"     => { "name" => "NODELIST", "ch" => "*", "l" => 1 },
    "end"       => { "name" => "END",      "ch" => "*", "l" => 1 },
    "longstart" => { "name" => "START",    "ch" => "*", "l" => 1 },
    "longend"   => { "name" => "END",      "ch" => "*", "l" => 1 },
    "unixstart" => { "name" => "START",    "ch" => "*", "l" => 1 },
    "unixend"   => { "name" => "END",      "ch" => "*", "l" => 1 }
   };

# Default format
@{$conf{format}}  = ();

# Short aliases for format specifiers
#
$conf{fmt_spec}{aliases} = {
        "jid" => "jobid", "part" => "partition", "st" => "state",
        "time" => "runtime", "time_s" => "runtime_s", "N" => "nnodes",
        "C" => "ncores" };


#############################################################################
#
#  State keys:
#
$conf{state_names} = {
    "COMPLETED"   => [ qw/ cd comp / ],
    "FAILED"      => [ qw/ f  fail / ],
    "CANCELLED"   => [ qw/ ca canc / ],
    "NODE_FAIL"   => [ qw/ nf node / ],
    "TIMEOUT"     => [ qw/ to time / ],
    "RUNNING"     => [ qw/ r  run  / ],
    "COMPLETING"  => [ qw/ cg cng  / ],
    "RESIZING"    => [ qw/ rs rsz  / ],
};

for my $st (keys %{$conf{state_names}}) {
    # Map abbreviation for state back to full state name:
    map { $conf{state_keys}{$_} = $st } (@{$conf{state_names}{$st}}, lc $st);
}

#############################################################################
#
#  Main program:
#

#  Initialize Date::Manip:
Date_Init ();

#  Initialize local conf object.
conf_init ();

#  Read configuration file(s)
read_config ();

#  Parse command line options.
parse_cmdline ();

#  Initialize DBI:
$conf{dbh}  = dbh_init ();

$conf{version}{1} = table_exists ($conf{dbh}, 'slurm_job_log');
$conf{version}{2} = table_exists ($conf{dbh}, 'jobs');

#  Compose a query based on options:
if ($conf{version}{1}) {
    $conf{stmt_v1} = compose_query (1, $conf{dbh}) 
                    or log_fatal ("Failed to generate query\n");
    log_verbose ("Running query: \"$conf{stmt_v1}\"\n");
}

#  Compose a query based on options:
if ($conf{version}{2}) {
    $conf{stmt_v2} = compose_query (2, $conf{dbh}) 
                    or log_fatal ("Failed to generate query\n");
    log_verbose ("Running query: \"$conf{stmt_v2}\"\n");
}

#  Don't do anymore if --dry-run was specified.
exit 0 if ($conf{dry_run});

#  Query running jobs if requested
query_running_jobs () unless ($conf{no_running});

#  Execute DB query and grab results:
if ($conf{version}{1}) {
    run_query (1, $conf{stmt_v1}) unless ($conf{nodb});
}

#  Execute DB query and grab results:
if ($conf{version}{2}) {
    run_query (2, $conf{stmt_v2}) unless ($conf{nodb});
}

#  Sort by requested keys
log_verbose ("sorting by keys @{$conf{sortkeys}}\n");
@{$conf{results}} = sort sort_by_keys (@{$conf{results}});

#  Limit results
$conf{total_results} = scalar @{$conf{results}};
if (@{$conf{results}} && $conf{limit} && (@{$conf{results}} > $conf{limit})) {
    @{$conf{results}} = @{$conf{results}}[0 .. $conf{limit} - 1];
} 

#  Apply any post-processing to results fields:
reformat_results (@{$conf{results}});

#  Create output format based on user options:
format_create ();

#  Output results:
write () for (@{$conf{results}});

if (($conf{total_results} > @{$conf{results}}) && !$conf{noheader}) {
    log_msg ("[More results available...]\n");
}

exit 0;

#############################################################################
#
#  Functions:
#

sub conf_init 
{

    $conf{got_exclude}            = 0;
    @{$conf{userfmt}}             = ();

    for (qw/jobids users states partitions nodes job_names/) {

        #
        #  Initialize include and exclude lists for this key:
        #
        @{$conf{$_}} = ();
        @{$conf{exclude}{$_}} = ();

        #
        #  By default matches are exact unless --regex is used.
        #
        $conf{regex}{$_} = 0;
        $conf{match_operator}{$_} = "IN";
    }

    for (qw/attime starttime endtime/) {
        $conf{$_}{qw/min t0 max/} = (0, 0, 0);
    }

    $conf{numnodes}               = -1;
    $conf{minnodes}               = -1;
    $conf{maxnodes}               = -1;
    $conf{numcores}               = -1;
    $conf{mincores}               = -1;
    $conf{maxcores}               = -1;
    $conf{runtime}                = -1;
    $conf{mintime}                = -1;
    $conf{maxtime}                = -1;
    $conf{listall}                = 0;
    $conf{dry_run}                = 0;
    $conf{noheader}               = 0;
    $conf{no_running}             = 0;
    $conf{nodb}                   = 0;
    $conf{verbose}                = 0;
    $conf{help}                   = 0;
}

sub read_user_config
{
    my $conf = "$ENV{HOME}/.sqlog";
    my $line = 0;

    return unless open (CONF, "$conf");

    while (<CONF>) {
        $line++;
        chomp;
        s/#.*$//;
        s/^\s+(.*)\s+$/$1/;
        next unless $_;

        my ($key, $value) = /(\S+) *= *(.*)/;

        if (!$key || !$value) {
            log_error ("$conf: $line: Parse error. Ignoring.\n");
            next;
        }

        if ($key =~ /format\{(.*)\}/) {
            $conf{format_list}{$1} = $value;
        }
        elsif ($key eq "limit") {
            $conf{limit} = $value;
        }
    }

    close (CONF);
}

sub read_config
{
    for my $f ("$conf{confdir}/sqlog.conf") {
        unless (my $rc = do $f) {
            log_fatal ("Couldn't parse $f: $@\n") if $@;
            next unless defined $rc;
            log_fatal ("couldn't run $f\n") unless $rc;
        }
        $conf{sqlhost} = $conf::SQLHOST if (defined $conf::SQLHOST);
        $conf{sqluser} = $conf::SQLUSER if (defined $conf::SQLUSER);
        $conf{db}      = $conf::SQLDB   if (defined $conf::SQLDB);

        # enable / disable per job node tracking
        $conf{track} = $conf::TRACKNODES if (defined $conf::TRACKNODES);

        if (defined %conf::FORMATS) {
            for my $key (keys %conf::FORMATS) {
                $conf{format_list}{$key} = $conf::FORMATS{$key};
            }
        }
    }

    read_user_config ();
}

sub parse_cmdline 
{
    my @sortkeys                  = ();
    my $starttime                 = "";
    my $maxstarttime              = "";
    my $minstarttime              = "";
    my $endtime                   = "";
    my $minendtime                = "";
    my $maxendtime                = "";
    my $runtime                   = "";
    my $minruntime                = "";
    my $maxruntime                = ""; 
    my $attime                    = "";

    my $rc = GetOptions (
        "help|h"         => \$conf{help},
        "dry-run"        => \$conf{dry_run},
        "no-header|H"    => \$conf{noheader},
        "verbose|v+"     => \$conf{verbose},
        "exclude|x"      => \$conf{got_exclude},
        "regex"          => \$conf{got_regex},
        "limit|L=i"      => \$conf{limit},
        "no-running|X"   => \$conf{no_running},
        "no-db"          => \$conf{nodb},
        "time|at|t=s"    => \$attime,

        "runtime|T=s"    => \$runtime,
        "mintime=s"      => \$minruntime,
        "maxtime=s"      => \$maxruntime,

        "start|S=s"      => \$starttime,
        "start-after=s"  => \$minstarttime,
        "start-before=s" => \$maxstarttime,

        "end|E=s"        => \$endtime,
        "end-after=s"    => \$minendtime,
        "end-before=s"   => \$maxendtime,

        "all|a"          => sub { $conf{limit} = 0      },
        "jobids|j=s"     => sub { append_excluded (@_); },
        "job-names|J=s"  => sub { append_excluded (@_); },
        "users|u=s"      => sub { append_excluded (@_); },
        "states|s=s"     => sub { append_excluded (@_); },
        "partitions|p=s" => sub { append_excluded (@_); },
        "nodes|n=s"      => sub { append_excluded (@_); },
        "format|o=s"     => sub { @{$conf{userfmt}} = split(/,/, $_[1]); },
        "sort|P=s"       => sub { @sortkeys = split(/,/, $_[1]); },
        "before|B=s"     => sub { set_time ("before", $_[1]); },
        "after|A=s"      => sub { set_time ("after",  $_[1]); },

        "nnodes|N=s"     => sub { set_count ("nodes", $_[1]);   },
        "minnodes=s"     => sub { set_count ("nodes", "+$_[1]") },
        "maxnodes=s"     => sub { set_count ("nodes", "-$_[1]") },

        "ncores|C=s"     => sub { set_count ("cores", $_[1]);   },
        "mincores=s"     => sub { set_count ("cores", "+$_[1]") },
        "maxcores=s"     => sub { set_count ("cores", "-$_[1]") },
        );

    usage () if $conf{help} || !$rc;

    parse_time_window ($attime) if ($attime);

    parse_start_time ($starttime, $minstarttime, $maxstarttime);
    parse_end_time ($endtime, $minendtime, $maxendtime);
    parse_runtime ($runtime, $minruntime, $maxruntime);

    $conf{limit} = $conf{listlimit} if $conf{listlimit};

    $conf{no_running} = 0 if $conf{nodb};

    set_format_list () or exit 1;

    check_state_lists () or exit 1;

    check_sort_keys (@sortkeys) or exit 1;

    for my $name (qw/ jobids users states partitions nodes /) {
        log_verbose ("$name: @{$conf{$name}}\n") if @{$conf{$name}};
        log_verbose ("excluded $name: @{$conf{exclude}{$name}}\n") 
            if @{$conf{exclude}{$name}};
    } 


}

sub list_valid_sort_keys
{
    log_msg ("Valid sort keys (prepend with '-' to reverse sort): \n", 
              join (', ', @{$conf{dbnames}}), "\n");
}

sub check_sort_keys
{
    my (@keys) = @_;
    my $failed = 0;

    @keys = @{$conf{sortkeys}} if (!@keys);

    for my $key (@keys) {
        #  Remove leading '-' if it exists and specify reverse sort
        #   for this key:
        #
        $conf{sortreverse}{$key}++ if ($key =~ s/^-//);

        #  Check for key in list of valid keys:
        #
        next if grep { $_ eq $key } @{$conf{dbnames}};

        log_error ("Invalid sort key '$key' specified.\n");
        $failed++;
    }

    if ($failed) {
        list_valid_sort_keys ();
        return 0;
    }

    @{$conf{sortkeys}} = @keys;
    return 1;
}

sub list_valid_states 
{
    my @keys = ();

    print STDERR "$progname: Valid state keys: ";

    for my $st (sort keys %{$conf{state_names}}) {
        push @keys, join ('|', map {uc} ($st, @{$conf{state_names}{$st}}));
    }

    print STDERR join ', ', @keys;
    print STDERR "\n";
}


sub check_state_lists
{
    my $failed = 0;

    for my $state (@{$conf{states}}, @{$conf{exclude}{states}}) {
        if (!exists $conf{state_keys}{lc $state}) {
            log_error ("Invalid state \"$state\" specified.\n");
            $failed++;
        }
    }

    if ($failed) {
        list_valid_states ();
        return 0;
    }

    @{$conf{states}} = map { $conf{state_keys}{lc $_} } @{$conf{states}};
    @{$conf{exclude}{states}} = 
              map { $conf{state_keys}{lc $_} } @{$conf{exclude}{states}};

    return 1;
}


sub list_format_keys
{
    print STDERR "$progname: Valid format keys: ";
    my @keys = ();
    for my $f (keys %{$conf{fmt_spec}{short}}) {
        my @a = grep { $conf{fmt_spec}{aliases}{$_} eq $f } 
                    keys %{$conf{fmt_spec}{aliases}};
                    
        push @keys, join '|', (@a, $f);
    }
    print STDERR join ', ', @keys;
    print STDERR "\n";

    print STDERR "$progname: Valid format types: ", 
                 join ',', grep { !/alias/ } keys %{$conf{fmt_spec}};
    print STDERR "\n";
}

sub check_format_list
{
    my $rc = 1;

    if (  (grep { $_[0] eq $_ } keys %{$conf{fmt_spec}})
       || ($_[0] =~ s/^(\w+)://)) {
        $conf{formattype} = $1 ||  shift @_;
        log_verbose ("set formattype = $conf{formattype}\n");
        log_fatal ("Invalid format type = \"$conf{formattype}\"\n") 
           unless exists $conf{fmt_spec}{$conf{formattype}};
    }

    for my $f (@_) {
        list_format_keys(), exit if ($f eq "list");

        next unless $f;
        
        if (  !exists $conf{fmt_spec}{aliases}{$f}
           && !exists $conf{fmt_spec}{short}{$f} ) {
           log_error ("Invalid format key \"$f\"\n");
           $rc = 0;
        }
    }

    # reset format if  there's anything on the list
    if (@_) {
        @{$conf{format}} = @_;
    }

    return $rc;
}

sub split_format 
{
    grep { $_ !~ /^\s*$/ } split (/,/, $_[0]);
}

sub set_format_list
{
    #
    #  Set the default
    #
    if (!@{$conf{userfmt}}) {
        @{$conf{userfmt}} = split_format($conf{format_list}{$conf{formattype}});
    }

    #
    #  Check for format type specification (e.g. long, freeform)
    #   and replace userfmt list with its specification
    #
    if ((scalar @{$conf{userfmt}} == 1)
       && (grep { $conf{userfmt}[0] eq $_ } keys %{$conf{format_list}}) ) {
        $conf{formattype} = $conf{userfmt}[0];
        my $fmt = $conf{format_list}{$conf{formattype}};
        @{$conf{userfmt}} = split_format ($fmt);
    } 

    #
    #  Check user provided format list
    #
    if (@{$conf{userfmt}}) {
        check_format_list (@{$conf{userfmt}}) or return undef;
    }

    log_fatal ("No format keys!") if (!@{$conf{format}});

    log_verbose ("format: @{$conf{format}}\n");
    return 1;
}

sub set_count
{
    my $field = shift @_;
    my ($num) = @_;

    if ($num =~ s/^\+(\d+)$/$1/) {
        $conf{"min$field"} = $num;
    } 
    elsif ($num =~ s/^-(\d+)/$1/) {
        $conf{"max$field"} = $num;
    }
    elsif ($num =~ /^(\d+)\.\.(\d+)$/) {
        $conf{"min$field"} = $1;
        $conf{"max$field"} = $2;
    }
    elsif ($num =~ /^\d+$/)  {
        $conf{"num$field"} = $num;
    }
    else {
        log_fatal ("--n$field: Invalid argument: $num\n");
    }
}

sub parse_datetime
{
    my ($arg) = @_;

    return "" if (!$arg);

    # Strip leading @
    $arg =~ s/^@//;

    my $date = parse_date ($arg)
        or log_fatal ("Invalid date string \"$arg\"\n");

    return UnixDate ($date, "%Y-%m-%d %H:%M:%S");
}

sub parse_time_range
{
    my ($time) = @_;

    my @t = split (/\.\./, $time);

    log_fatal ("Invalid date and time or range: $time\n") if (@t > 2);

    return map { parse_datetime ($_) } @t;
}

sub parse_time_window 
{
    $conf{attime} = parse_minmax_time (@_);
}

sub parse_minmax_time
{
    my ($time) = @_;

    if ($time =~ s/^\+(.*)$/$1/) {
        return { "min" => parse_datetime ($time) };
    }
    elsif ($time =~ s/^-(.*)/$1/) {
        return { "max" => parse_datetime ($time) };
    }
    else {
        my ($t1, $t2) = parse_time_range ($time);
        $t2 && return { "min" => $t1, "max" => "$t2" };
        return { "equal" => $t1 };
    }
}

sub parse_start_time
{
    my ($time, $min, $max) = @_;

    log_fatal ("Do not specify --start with either" .
               " --start-before or --start-after.\n")
       if ($time && ($min || $max));

    $conf{starttime}{min} = parse_datetime ($min) if ($min);
    $conf{starttime}{max} = parse_datetime ($max) if ($max); 
    $conf{starttime} = parse_minmax_time ($time) if ($time);
}

sub parse_end_time
{
    my ($time, $min, $max) = @_;

    log_fatal ("Do not specify --end with either" .
               " --end-before or --end-after.\n")
       if ($time && ($min || $max));

    $conf{endtime}{min} = parse_datetime ($min) if ($min);
    $conf{endtime}{max} = parse_datetime ($max) if ($max); 
    $conf{endtime} = parse_minmax_time ($time) if ($time);
}

sub parse_runtime
{
    my ($time, $min, $max) = @_;

    return if ($time eq "" && $min eq "" && $max eq "");

    if ($time ne "" && ($min ne "" || $max ne "")) {
        log_fatal ("Do not specify --runtime with --mintime or --maxtime.\n");
    }

    $time = format_minmax_string ($time, $min, $max);

    if ($time =~ /^\+(.*)$/) {
        $conf{mintime} = duration_to_seconds ($1);
        log_fatal ("Invalid min runtime specified: $1\n") 
            if !defined $conf{mintime};
    } 
    elsif ($time =~ /^-(.*)/) {
        $conf{maxtime} = duration_to_seconds ($1);
        log_fatal ("Invalid max runtime specified: $1\n") 
            if !defined $conf{maxtime};
    }
    elsif ($time =~ /^([^-]+)\.\.([^-]+)$/) {
        $conf{mintime} = duration_to_seconds ($1);
        $conf{maxtime} = duration_to_seconds ($2);
        log_fatal ("Invalid max runtime specified: $1\n") 
            if !defined $conf{mintime};
        log_fatal ("Invalid min runtime specified: $2\n") 
            if !defined $conf{maxtime};
    }
    else {
        # strip any leading @
        $time =~ s/^@//;
        $conf{runtime} = duration_to_seconds ($time);
        log_fatal ("Invalid runtime specified: $time\n") 
            if !defined $conf{runtime};
    }
}

#
#  Turn optional arguments value, min, max, into a string
#   describing the value, minimum, maximum, or range.
#
sub format_minmax_string 
{
    my ($val, $min, $max) = @_;
    return ($val) if ($val ne "");
    return ("$min\.\.$max") if ($min ne "" && $max ne "");
    return ("-$max")     if ($max ne "");
    return ("+$min")     if ($min ne ""); 
    return "";
}


#  Convert a duration to seconds. 
#
#  Valid duration strings include the common SLURM form D-HH:MM:SS
#    or the form 3H
#
sub duration_to_seconds
{
    my ($t) = @_;
    my ($d, $h, $m, $s);

    #
    #  list of valid regexes to check in order.
    #  1. DD-HH:MM:SS type 
    #  2. 1hr20min or 1h20m type
    #
    my @regexes = qw/
      ^(?:(\d+)(?:-))?(\d*?):?(\d*?):(\d+)$
      ^(\d*?)(?i:d|days?)?(\d*?)(?i:hr?)?(\d*?)(?i:m|min)?(\d*)(?i:s||sec)?$
      NOTFOUND
      /;

    for my $re (@regexes) {
        return undef if ($re eq "NOTFOUND");
        last if (($d, $h, $m, $s) = ($t =~ /$re/)) 
    }

    log_debug ("duration_to_seconds ($t):",
               " d=", $d||0,
               " h=", $h||0,
               " m=", $m||0,
               " s=", $s||0,
               "\n");

    return (($s||0) + ($m||0) * 60 + ($h||0) * 3600 + ($d||0) * 3600 * 24);
}

#
#  Append job filter options to global configuration, appending to
#   the "excluded" lists if --exclude was used.
#
#  Also, if --regex was specified before this parameter, then add this
#   key to the global regex config option.
#
sub append_excluded
{
    my ($name, $arg) = @_;
    my $exclude = $conf{got_exclude};

    $name =~ s/-/_/g;

    #
    #  If --regex was used before this option, then note that the
    #   argument for $name is a regex:
    #
    if ($conf{got_regex}) {
        $conf{regex}{$name} = 1;
        $conf{match_operator}{$name} = "REGEXP";
    }

    my $list = $exclude ? $conf{exclude}{$name} : $conf{$name};

    if ($name ne "nodes")  {
        push (@$list, split (/,/, $arg));
    } 
    else  {
        push (@$list, expand ($arg));
    }

    # Nullify exclude and regex options for next argument:
    $conf{got_exclude} = 0;
    $conf{got_regex} = 0;
}

sub format_runtime
{
    my ($t) = @_;

    my $sec = $t % 60;
    my $min = ($t / 60)   % 60;
    my $hrs = ($t / 3600) % 24;
    my $dys = $t / 86400;

    int $dys && return sprintf ("%d-%2.2d:%2.2d:%2.2d", $dys, $hrs, $min, $sec);
    int $hrs && return sprintf (      "%d:%2.2d:%2.2d", $hrs, $min, $sec);
    int $min && return sprintf (            "%d:%2.2d", $min, $sec);
    return sprintf             (              ":%2.2d", $sec);
}

sub format_create
{
    my $common = "";
    my $format = "";
    my $format_top = "";
    my $values = "";

    my @form = @{$conf{format}};

    my $fmt = $conf{fmt_spec}{$conf{formattype}};
    my $alias = $conf{fmt_spec}{aliases};

    for my $f (@form) {
        $f = $$alias{$f} if (!exists $$fmt{$f});
        next unless $f;
        if (!exists $$fmt{$f}) {
            log_error ("Skipping invalid format key \"$f\"\n");
            next;
        }
        $common .= "@" . $$fmt{$f}{ch} x $$fmt{$f}{l} . " ";
    }
    $common .= "\n";


    $format = "format STDOUT = \n";
    $format .= $common;
    $format .= join ',', map { '$$_{' . $_  . '}' } @form;
    $format .= "\n.\n";

    $format_top = "format STDOUT_TOP = \n";
    $format_top .= $common;
    $format_top .= join ',', map { '"' . $$fmt{$_}{name} . '"' } @form;
    $format_top .= "\n.\n";

    eval $format;
    eval $format_top unless $conf{noheader};
}

sub nodes_not_included
{
    my (@nodes) = expand (@_);
    
    if (@{$conf{nodes}} && !intersect (\@nodes, \@{$conf{nodes}})) {
        return 1;
    }

    if (  @{$conf{exclude}{nodes}} 
       && intersect (\@nodes, \@{$conf{exclude}{nodes}})) {
       return 1;
    }

    return 0;
}

sub state_name 
{
    my ($s) = @_;
    my %states = (
        "COMPLETED" => { "short" => "CD", "long" => "completed" },
        "FAILED"    => { "short" => "F",  "long" => "failed"   }, 
        "CANCELLED" => { "short" => "CA", "long" => "cancelled" }, 
        "NODE_FAIL" => { "short" => "NF", "long" => "nodefail" },
        "TIMEOUT"   => { "short" => "TO", "long" => "timeout"  },
        "RUNNING"   => { "short" => "R",  "long" => "running" },
        "COMPLETING"=> { "short" => "CG", "long" => "completing" },
        "RESIZING"  => { "short" => "RS", "long" => "resizing" }
    );
    log_error ("Unknown state \"$s\"\n") if (!exists $states{$s});
    my $l = $conf{formattype} eq "short" ? "short" : "long";
    return $states{$s}{$l}; 
}
##############################################################################
#
#  Sort Routines
#

$conf{datecache} = ();

sub parse_date
{
    my ($date) = @_;
    return "" if (!defined $date);
    return $conf{datecache}{$date} if (exists $conf{datecache}{$date});
    return $conf{datecache}{$date} = ParseDate ($date);
}

sub sort_date
{
    my ($d1, $d2) = map { /NONE/ ? "+10y" : $_ }  @_;
    return Date_Cmp (parse_date ($d1), parse_date ($d2));
}

sub sort_start   { return sort_date ($$a{start}, $$b{start}); }
sub sort_end     { return sort_date ($$a{end}, $$b{end}); }
sub sort_runtime { return $$a{runtime} <=> $$b{runtime}; }
sub sort_id      { return $$a{id}||0 <=> $$b{id}||0 };
sub sort_jobid   { return $$a{jobid} <=> $$b{jobid} };
sub sort_limit   { return $$a{limit} cmp $$b{limit} };
sub sort_user    { return $$a{user} cmp $$b{user} };
sub sort_uid     { return $$a{uid} <=> $$b{uid} };
sub sort_name    { return $$a{name} cmp $$b{name} };
sub sort_state   { return $$a{state} cmp $$b{state} };
sub sort_partition { return $$a{partition} cmp $$b{partition} };
sub sort_nodes   { return $$a{nodes} cmp $$b{nodes} };
sub sort_nnodes  { return $$a{nnodes} <=> $$b{nnodes} };
sub sort_ncores  { return $$a{ncores} <=> $$b{ncores} };

sub sort_by_keys
{
    my $rc = 0;
    no strict 'refs';

    for (@{$conf{sortkeys}}) {
        $rc = &{"sort_$_"} ($a, $b);
        return ($conf{sortreverse}{$_} ? $rc : -$rc) if ($rc);
    }
    return $rc;
}

sub sort_by_start
{
    # create a cache of Date::Manip  objects
    $$_{startdate} = parse_date ($$_{start}) for (@_);
    
    # sort by start date
    sort { Date_Cmp ($$a{startdate}, $$b{startdate}) } @_; 
}

##############################################################################
#
# DB routines
#

# returns 1 if table exists, 0 otherwise
sub table_exists
{
    my $dbh   = shift @_;
    my $table = shift @_;

    # check whether our database has a table by the proper name
    my $sth = $dbh->prepare("SHOW TABLES;");
    if ($sth->execute()) {
        while (my ($name) = $sth->fetchrow_array()) {
            if ($name eq $table) { return 1; }
        }
    }

    # didn't find it
    return 0;
}

sub get_seconds_date_manip_is_buggy
{
    my ($date) = @_;
    use Time::Local;

    return "NONE" if ($date eq "NONE");

    my $S = UnixDate ($date, "%S");
    my $M = UnixDate ($date, "%M");
    my $H = UnixDate ($date, "%H");

    my $d = UnixDate ($date, "%e");
    my $m = UnixDate ($date, "%m");
    my $y = UnixDate ($date, "%Y");

    if (!($S && $M && $H && $d && $m && $y)) {
        &log_verbose ("Malformed date string: \"$date\". Returning NONE.\n");
        return "NONE";
    }

    $m -= 1;
    $y -= 1900;

    &log_debug ("timelocal ($S, $M, $H, $d, $m, $y)\n");

    return timelocal ($S, $M, $H, $d, $m, $y);
}

sub reformat_results
{
    my (@rows) = @_;
    my $year = (localtime (time))[5] + 1900;
    for my $r (@rows) {
        next if (!$r->{start});

        # Drop year for shorter start and end times.
        #  Save year for possible inclusion in output.
        $$r{longend} = $$r{end};
        $$r{unixend} = &get_seconds_date_manip_is_buggy ($$r{end});
        $$r{longstart} = $$r{start};
        $$r{unixstart} = &get_seconds_date_manip_is_buggy ($$r{start});

        $$r{end} =~ s#^(\d+)-(\d+)-(\d+) #$2/$3-#;
        $$r{start} =~ s#^(\d+)-(\d+)-(\d+) #$2/$3-#;

        # Reformat long output so it all looks the same.
        $$r{longend} =~ s#^(\d+)-(\d+)-(\d+) #$1-$2-$3T# 
            || $$r{longend} =~ s#^(\d+)/(\d+)-#$year-$1-$2T#;

        if (!($$r{longstart} =~ s#^(\d+)-(\d+)-(\d+) #$1-$2-$3T#)) {
            #  If start date&time is after end date&time, then probably
            #   the start date is last year
            my $s = parse_date ($$r{start});
            my $e = parse_date ($$r{end});
            $year-- if (Date_Cmp ($s, $e) > 0);
            $$r{longstart} =~ s#^(\d+)/(\d+)-#$year-$1-$2T#;
        }


        #  Save runtime in seconds in case user wants to print that
        $$r{runtime_s} = $$r{runtime};
        $$r{runtime} = format_runtime ($$r{runtime});

        # Alternate state name
        $$r{state} = state_name ($$r{state});
    }
}

sub run_query
{
    my $version = shift @_;
    my $stmt    = shift @_;

    my $sth = $conf{dbh}->prepare ($stmt);

    if (not $sth->execute ()) {
        # may be something fatal, or may just be that a
        # particular table version does not exist
        log_verbose ("Query failed: $stmt\n");
        return;
    }
    log_verbose ("Success.\n");

    my @names = qw/ id jobid username_id uid jobname_id jobstate_id
                    partition_id limit start end runtime
                    nodes nnodes ncores user name state partition /;
    if ($version == 1) {
        # no ncores field in record for version 1 of the schema
        @names = qw/ id jobid user uid name state partition limit start end 
                    nodes nnodes runtime /;
    }

    my $count = 0;
    while (my @vals = $sth->fetchrow_array ()) {
        my %r = ();
        @r{@names} = @vals;

        if ($version == 1) {
          # cores were not recorded in version 1 schema
          $r{ncores} = "";
        }
        if ($version == 1 or not $conf{track}) {
          # we have to do the nodename checking by hand
          next if nodes_not_included ($r{nodes});
        }

        push (@{$conf{results}}, \%r);
        $count++;
    }

    log_verbose ("Fetched $count rows.\n");
}

sub dbh_init
{
    my $dbh = DBI->connect ("DBI:mysql:database=$conf{db};host=$conf{sqlhost}",
                            $conf{sqluser}, $conf{sqlpass})
        or log_fatal("Failed to connect to DB at $conf{sqlhost}\n");

    log_verbose ("Connected to database $conf{db} at host $conf{sqlhost} " .
                 "as user $conf{sqluser}\n");

    return $dbh;
}

sub quote_list
{
    my ($dbh, @list) = @_;
    return "(" . join (',', (map { $dbh->quote ($_) } @list)) . ")";
}

#
#  Return the ids list from the corresponding table for job data $name
#   given the filter arguments $arg:
#
sub lookup_ids
{
    my ($dbh, $key, $args) = @_;
    my @ids = ();
    my %tables = (
        "users"      => "usernames",
        "states"     => "jobstates",
        "partitions" => "partitions",
        "job_names"  => "jobnames",
        "nodes"      => "nodes"
    );

    return (@ids) if !@$args;

    &log_debug ("lookup_ids (DBH, key=$key, args=\"@$args\")\n");

    # lookup matching names from table and insert ids
    my $sql = "SELECT * FROM `$tables{$key}`" .
              " WHERE `name` " .
              $conf{match_operator}{$key} . " " .
              quote_list ($dbh, @$args) . ";";

    my $sth = $dbh->prepare($sql);

    log_verbose ("Running query: \"$sql\"\n");

    if ($sth->execute()) {
        while (my ($id, $name) = $sth->fetchrow_array ()) {
            push @ids, $id;
        }
    }

    return @ids;
}

sub stmt_list
{
    my ($version, $dbh, $excluded) = @_;
    my %names = (
        "jobids"     => "jobid",
        "users"      => "username",
        "states"     => "jobstate",
        "partitions" => "partition",
        "job_names"  => "jobname" );

    my @result = ();

    my $operator = $excluded ? "NOT IN" : "IN";

    # for version 2, we need to lookup ids for
    # users,states,partions,and jobnames
    if ($version > 1) {
        my %fields = (
            "users"      => "username_id",
            "states"     => "jobstate_id",
            "partitions" => "partition_id",
            "job_names"  => "jobname_id",
        );

        foreach my $key (keys %fields) {

            #
            #  Lookup ids (included or excluded) for key $key
            #   and store in include_ids or exclude_ids list.
            #
            my $listref = \@{$conf{$key}};
            my $type =   'include_ids';

            if ($excluded) {
                $listref = \@{$conf{exclude}{$key}};
                $type    = 'exclude_ids';
            }

            @{$conf{$type}{$key}} = &lookup_ids ($dbh, $key, $listref);
        }

        # first insert jobids
        my $list = $excluded ?
                   \@{$conf{exclude}{"jobids"}} :
                   \@{$conf{"jobids"}};
        if (@$list) {
            push (@result, "`$names{jobids}` $operator " . quote_list ($dbh, @$list));
        }

        # now build IN/NOT IN lists of remaining fields using their id values
        for my $key (keys %fields) {
            # choose included or excluded list
            my $list     = $excluded ?
                           \@{$conf{exclude}{$key}} : 
                           \@{$conf{$key}};
            my $list_ids = $excluded ? 
                           \@{$conf{exclude_ids}{$key}} :
                           \@{$conf{include_ids}{$key}};

            # for this key, want to filter by names and a list of
            # names was specified, but no matching ids were found,
            # so set query to return NULL set
            if (not $excluded and @$list and not @$list_ids) {
                push @result, "1 = 0";
            }

            # skip empty lists
            next unless @$list_ids;

            push (@result, "`$fields{$key}` $operator " .
                           quote_list ($dbh, @$list_ids));
        }
    } else {
        # For all keys in %names:
        #  - Choose included or excluded list
        #  - skip empty lists
        #  - generate one list entry with "NAME (NOT) IN ( 'item', ... )"
        #
        for my $name (keys %names) {
            my $list = $excluded ?
                       \@{$conf{exclude}{$name}} :
                       \@{$conf{$name}};
            next unless @$list;
            push (@result, "$names{$name} $operator " .
                           quote_list ($dbh, @$list));
        }
    }

    return (@result);
}

sub order_by
{
    my $version = shift @_;
    my @s = ();
    for my $key (@{$conf{sortkeys}}) {
        my $field = $conf{sortfield}{$key};
        if ($version == 1 and $field eq "corecount") {
            # corecount field wasn't added until version 2
            next;
        }
        push (@s, "$field " . ($conf{sortreverse}{$key} ? "ASC" : "DESC"));
    }

    return "" if (!@s);

    return " ORDER BY " . join ',', @s;
}

sub compose_query 
{
    my $version = shift @_;
    my ($dbh) = @_;

    #
    # Generate list of statements for included and excluded jobids, users,
    #  states, and partitions:
    #
    my @stmts = (stmt_list ($version, $dbh, 0), stmt_list ($version, $dbh, 1));
    my @have = ();

    if ($conf{attime}{min} or $conf{attime}{max}) {
        # all jobs which did not end before min and start after max
        my $min = $conf{attime}{min};
        my $max = $conf{attime}{max};
        my $stmt = "(NOT (";
        $stmt .= "endtime < "   . $dbh->quote($min) if ($min);
        $stmt .= " OR " if ($min and $max); 
        $stmt .= "starttime > " . $dbh->quote($max) if ($max);
        $stmt .= "))";
        push (@stmts, $stmt);
    } 
    elsif ($conf{attime}{equal}) {
        push (@stmts, "starttime <= " . $dbh->quote ($conf{attime}{equal}) 
                      . " AND " .
                      "endtime >= "   . $dbh->quote ($conf{attime}{equal}));
    }

    # for certain end conditions, we can assume certain start conditions
    # and vice versa, e.g., if a job ends at or before X, it must have
    # also started at or before X.  Note that this may exclude records
    # with errant data, where a job's start time actually is recorded
    # as later than its end time, but the payoff is that for common queries
    # this method defines a window of records using either the starttime
    # or endtime indices
    if ($conf{endtime}{equal}) { 
        # if job ended at a specific time,
        # it must have started at or before that time
        push (@stmts, "endtime = "    .  $dbh->quote ($conf{endtime}{equal})); 
        push (@stmts, "starttime <= " .  $dbh->quote ($conf{endtime}{equal})); 
    }
    if ($conf{endtime}{max}) { 
        # if job ended at or before a specific time,
        # it must have started at or before that time
        push (@stmts, "endtime <= "   .  $dbh->quote ($conf{endtime}{max})); 
        push (@stmts, "starttime <= " .  $dbh->quote ($conf{endtime}{max})); 
    }
    if ($conf{endtime}{min})  { 
        # ending after a certain time does not imply
        # any reasonable condition on start time
        push (@stmts, "endtime >= " . $dbh->quote ($conf{endtime}{min})); 
    }

    if ($conf{starttime}{equal}) { 
        # if job started at a specific time,
        # it must have also ended at or after that time
        push (@stmts, "starttime = " . $dbh->quote ($conf{starttime}{equal})); 
        push (@stmts, "endtime >= "  . $dbh->quote ($conf{starttime}{equal})); 
    }
    if ($conf{starttime}{max}) { 
        # starting before a certain time does not imply
        # any reasonable condition on end time
        push (@stmts, "starttime <= " . $dbh->quote ($conf{starttime}{max})); 
    }
    if ($conf{starttime}{min})  { 
        # if job started at or after a specific time,
        # it must have also ended at or after that time
        push (@stmts, "starttime >= " . $dbh->quote ($conf{starttime}{min})); 
        push (@stmts, "endtime >= "   . $dbh->quote ($conf{starttime}{min})); 
    }

    if ($conf{numnodes} >= 0) { 
        push @stmts, "nodecount = "  . $dbh->quote($conf{numnodes});
    }
    if ($conf{minnodes} >= 0) { 
        push @stmts, "nodecount >= " . $dbh->quote($conf{minnodes});
    }
    if ($conf{maxnodes} >= 0) { 
        push @stmts, "nodecount <= " . $dbh->quote($conf{maxnodes});
    }

    # corecount field wasn't added until version 2 of the schema
    if ($version > 1) {
        if ($conf{numcores} >= 0) { 
            push @stmts, "corecount = "  . $dbh->quote($conf{numcores});
        }
        if ($conf{mincores} >= 0) { 
            push @stmts, "corecount >= " . $dbh->quote($conf{mincores});
        }
        if ($conf{maxcores} >= 0) { 
            push @stmts, "corecount <= " . $dbh->quote($conf{maxcores});
        }
    }

    if ($version > 1) {
        # runtime is a real field with version 2
        if ($conf{runtime} >= 0) { 
            push @stmts, "runtime = "  . $dbh->quote($conf{runtime});
        }
        if ($conf{mintime} >= 0) { 
            push @stmts, "runtime >= " . $dbh->quote($conf{mintime});
        }
        if ($conf{maxtime} >= 0) { 
            push @stmts, "runtime <= " . $dbh->quote($conf{maxtime});
        }
    } else {
        # we construct runtime dynamically for each record,
        # so it must be in the HAVING clause in version 1
        if ($conf{runtime} >= 0) { 
            push @have, "runtime = "  . $dbh->quote($conf{runtime});
        }
        if ($conf{mintime} >= 0) { 
            push @have, "runtime >= " . $dbh->quote($conf{mintime});
        }
        if ($conf{maxtime} >= 0) { 
            push @have, "runtime <= " . $dbh->quote($conf{maxtime});
        }
    }

    # need to query for node ids
    my $node_join = "";
    if ($version > 1 and $conf{track} and
        (@{$conf{nodes}} or @{$conf{exclude}{nodes}})
       )
    {
        # lookup ids for lists of node names to include or exclude
        @{$conf{include_ids}{nodes}} =
                &lookup_ids ($dbh, "nodes", \@{$conf{nodes}});
        @{$conf{exclude_ids}{nodes}} =
                &lookup_ids ($dbh, "nodes", \@{$conf{exclude}{nodes}});

        # list of nodes to include specified, but no matching ids found,
        # set WHERE to return NULL set
        if (@{$conf{nodes}} and not @{$conf{include_ids}{nodes}}) {
            push @stmts, "1 = 0";
        }

        # set where clauses to pick out or exclude node ids
        my @joins = ();
        my $jn = 1;

        # handle the included nodes first
        my @includes = ();
        foreach my $id (@{$conf{include_ids}{nodes}}) {
            push @joins, "`jobs_nodes` as `jn$jn`" .
                         " ON `jobs`.`id` = `jn$jn`.`job_id`" .
                         " AND `jn$jn`.`node_id` = $id";
            push @includes, "`jn$jn`.`node_id` IS NOT NULL";
            $jn++;
        }

        # each record must include at least one of the listed include nodes
        if (@includes) {
            push @stmts, "(" . join(" OR ", @includes) . ")";
        }

        # now the excluded nodes
        if (@{$conf{exclude_ids}{nodes}}) {
            foreach my $id (@{$conf{exclude_ids}{nodes}}) {
                push @joins, "`jobs_nodes` as `jn$jn`" .
                             " ON `jobs`.`id` = `jn$jn`.`job_id`" .
                             " AND `jn$jn`.`node_id` = $id";
                # no record may include any of the excluded nodes
                push @stmts, "`jn$jn`.`node_id` IS NULL";
                $jn++;
            }
        }

        # left join jobs with jobs_nodes tables
        if (@joins) {
            $node_join = " LEFT JOIN " . join(" LEFT JOIN ", @joins);
        }
    }

    # set the table name (version dependent)
    my $stmt = "";
    if ($version > 1) {
        $stmt = "SELECT" .
        " `jobs`.*," .
        "`usernames`.`name` as `username`," .
        "`jobnames`.`name` as `jobname`," .
        "`jobstates`.`name` as `jobstate`," .
        "`partitions`.`name` as `partition`" .
        " FROM `jobs`" .
        " LEFT JOIN `usernames`  ON `jobs`.`username_id`  = `usernames`.`id`" .
        " LEFT JOIN `jobnames`   ON `jobs`.`jobname_id`   = `jobnames`.`id`" .
        " LEFT JOIN `jobstates`  ON `jobs`.`jobstate_id`  = `jobstates`.`id`" .
        " LEFT JOIN `partitions` ON `jobs`.`partition_id` = `partitions`.`id`" .
        $node_join;
    } else {
        $stmt = "SELECT *," .
                "UNIX_TIMESTAMP(endtime)-UNIX_TIMESTAMP(starttime) " .
                "as runtime FROM `slurm_job_log`";
    }

    $stmt .= " WHERE "  . join " AND ", @stmts if (@stmts);
    $stmt .= " HAVING " . join " AND ", @have  if (@have);
    $stmt .= order_by ($version);

    #  If there is a limit enforced, get no more results than one
    #   more than the imposed limit.
    #
    if ($version > 1 and $conf{track}) {
        if ($conf{limit}) {
            $stmt .= " LIMIT " . (($conf{listlimit} || $conf{limit}) + 1);
        }
    } else {
        # disable limit if include or exclude nodes is set,
        # need to get all records and check nodes by hand
        if ($conf{limit} && !@{$conf{nodes}} && !@{$conf{exclude}{nodes}}) {
            $stmt .= " LIMIT " . (($conf{listlimit} || $conf{limit}) + 1);
        }
    }

    $stmt .= ";";

    return ($stmt);
}

##############################################################################
#
#  Query running jobs with squeue.
#

#
#  Return true if the value $val of job variable $var was not included
#   or was explictly excluded by any command line options. o/w return false.
#
sub var_not_included
{
    my ($var, $val) = @_;

    #
    #  If --regex was supplied, then user-supplied options are regexes,
    #   o/w treat them as an exact match.
    #
    my $sub = $conf{regex}{$var} ?
        sub {$_[0] =~ m/$_[1]/} : sub {$_[0] eq $_[1]};

    #
    #  If there were any supplied query arguments for job variable $var
    #   (e.g. job_names, users, etc) then return true if $val doesn't
    #   match at least one of these arguments:
    #
    if (@{$conf{$var}} && !grep ( {&$sub($val, $_)} @{$conf{$var}})) {
        return 1;
    }

    #
    #  If any job variables $var were explicitly excluded, then return true
    #   immediately.
    #
    if (@{$conf{exclude}{$var}} &&
        grep ({&$sub($val, $_)} @{$conf{exclude}{$var}})) {
        return 1;
    }

    return 0;
}

#
#  Given a reference to a hash of job information, return true
#   if this job is not included due to any list of command-line
#   parameters issued by the sqlog user.
#
sub job_not_included
{
    my ($jobinfo) = @_;

    #
    #  The job_keymap is the list of global config keywords
    #   to be checked for inclusion/exclusion, as well as the key name
    #   for these parameters in the job info hash %$jobinfo.
    #
    #  Note: this keymap must be updated whenever a non-numeric job
    #   search key is added to sqlog.
    #
    my %job_keymap = (
        "jobids" => "jobid",
        "job_names" => "name",
        "users" => "user",
        "states" => "state",
        "partitions" => "partition",
    );
    #
    #  Loop over each job key in job_keymap and check whether this
    #   job variable was not included or explicitly excluded by
    #   command line options.
    #
    for my $key (keys %job_keymap) {
        return 1 if var_not_included ($key, $$jobinfo{$job_keymap{$key}});
    }
    return 0;
}


sub time_not_included
{
    my ($type, $time) = @_;

    my $start = parse_date ($time);
    my $equal = parse_date ($conf{$type}{equal});
    my $min   = parse_date ($conf{$type}{min});
    my $max   = parse_date ($conf{$type}{max});

    log_debug ("Checking $type $start == $equal\n"); 
    return 1 unless (!$equal || Date_Cmp ($start, $equal) == 0);
    log_debug ("Checking $type $start <= $max\n"); 
    return 1 unless (!$max   || Date_Cmp ($start, $max)   <= 0);
    log_debug ("Checking $type $start >= $min\n"); 
    return 1 unless (!$min   || Date_Cmp ($start, $min)   >= 0);
    log_debug ("Checking $type $start not excluded\n"); 
    return 0;
}

sub starttime_not_included
{
    my ($start) = @_;
    return time_not_included ("starttime", $start);
}

sub endtime_not_included
{
    my ($end) = @_;
    return time_not_included ("endtime", $end);
}

sub excluded_by_time_window
{
    my ($start, $end) = @_;

    my $min  = parse_date ($conf{attime}{min});
    my $max  = parse_date ($conf{attime}{max});
    my $time = parse_date ($conf{attime}{equal});

    return 0 if (!$min && !$max && !$time);

    $start = parse_date ($start);
    $end = parse_date ($end);

    log_verbose ("min=$min, max=$max, start=$start, end=$end\n");

    return 1 if ($max && Date_Cmp ($start, $max) > 0);
    return 1 if ($min && Date_Cmp ($end,   $min) < 0);
    if ($time) {
        return 1 unless (Date_Cmp ($start, $time) <= 0);
        return 1 unless (Date_Cmp ($end,   $time) >= 0);
    }
    return 0;
}

sub reformat_squeue_datetime
{
    my ($date) = @_;
    $date =~ s/(\d{4}-\d{2}-\d{2})T(\d{2}:\d{2}:\d{2})/$1 $2/;
    return $date;
}

sub query_running_jobs
{
    my @names = qw/ jobid user uid name state partition limit start end 
                        nodes nnodes ncores runtime /;

    my $sfmt = "%i::%u::%U::%j::%T::%P::%l::%S::%e::%N::%D::%C::%M";

    my $cmd = "squeue -h -t running,completing -o $sfmt";

    open (SQ, "$cmd |") or log_error ("Failed to run squeue: $!\n");

    while (<SQ>) {
        my %r = ();
        chomp;
        @r{@names} = split (/::/, $_);

        #
        # Handle ENDTIME == None for running jobs
        #
        $r{end} = "2999-12-31-00:00:00" if $r{end} =~ m/(none|unknown)/i;

        #
        #  Drop any "T" in datetime string
        #
        $r{start} = &reformat_squeue_datetime ($r{start});
        $r{end} = &reformat_squeue_datetime ($r{end});

        $r{runtime} = duration_to_seconds ($r{runtime});

        next if excluded_by_time_window ($r{start}, $r{end});

        next if (($conf{numnodes} >= 0) && !($r{nnodes} == $conf{numnodes}) );
        next if (($conf{minnodes} >= 0) && !($r{nnodes} >= $conf{minnodes}) );
        next if (($conf{maxnodes} >= 0) && !($r{nnodes} <= $conf{maxnodes}) );

        next if (($conf{numcores} >= 0) && !($r{ncores} == $conf{numcores}) );
        next if (($conf{mincores} >= 0) && !($r{ncores} >= $conf{mincores}) );
        next if (($conf{maxcores} >= 0) && !($r{ncores} <= $conf{maxcores}) );

        next if (($conf{runtime} >= 0)  && !($r{runtime} == $conf{runtime}) );
        next if (($conf{mintime} >= 0)  && !($r{runtime} >= $conf{mintime}) );
        next if (($conf{maxtime} >= 0)  && !($r{runtime} <= $conf{maxtime}) );

        next if starttime_not_included ($r{start});
        next if endtime_not_included ($r{end});
        next if nodes_not_included ($r{nodes});
        next if job_not_included (\%r);

        push (@{$conf{results}}, \%r);

        #last if ($conf{limit} && (@{$conf{results}} == $conf{limit}));
    }

    log_verbose (scalar @{$conf{results}}, " running jobs selected.\n");
    close (SQ);
}

##############################################################################
#
# Logging functions
#
sub log_msg     { print STDERR "$progname: ", @_; }
sub log_verbose { log_msg (@_) if ($conf{verbose} > 0); }
sub log_debug   { log_msg (@_) if ($conf{verbose} > 1); }
sub log_error   { log_msg ("Error: ", @_) }
sub log_fatal   { log_msg ("Fatal: ", @_); exit 1; }
sub usage       { print STDERR $conf{usage}; exit 0; }

# vi: ts=4 sw=4 expandtab
